﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Model;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Controller
{
    public class KPIAchievementController : Controller.dbConnection.ConnectionClass
    {
        public IList<KPIAchievement> GetKPIAchievementTable(int year, int KPIAssignID)
        {
            List<KPIAchievement> KPIs = new List<KPIAchievement>();

            for (int h = 1; h <= 12; h++)
            {
                SqlCommand command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "SP_GetKPIAchievementTable";
                command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
                command.Parameters.Add("@KPIASSIGNID", SqlDbType.Int).Value = KPIAssignID;
                command.Parameters.Add("@MONTH", SqlDbType.Int).Value = h;

                DataSet dx = GetTable(command);

                DataTable dt = dx.Tables[0].DefaultView.ToTable();

                if (dx.Tables[0].Rows.Count > 0)
                {
                    KPIAchievement p = new KPIAchievement();
                    p.ID = Convert.ToInt16(dx.Tables[0].Rows[0]["ID"].ToString());
                    p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[0]["KPIAssignID"].ToString()));
                    p.Month = Convert.ToInt32(dx.Tables[0].Rows[0]["Month"].ToString());
                    p.Year = Convert.ToInt32(dx.Tables[0].Rows[0]["Year"].ToString());
                    p.Realization = dx.Tables[0].Rows[0]["Realization"].ToString();
                    p.Score = dx.Tables[0].Rows[0]["Score"].ToString();
                    p.StatusByUser = new KPIStatusController().GetKPIStatusByID(Convert.ToInt16(dx.Tables[0].Rows[0]["StatusByUser"] == DBNull.Value ? "0" : dx.Tables[0].Rows[0]["StatusByUser"].ToString()));
                    p.StatusByVerificator = new KPIStatusController().GetKPIStatusByID(Convert.ToInt16(dx.Tables[0].Rows[0]["StatusByverificator"] == DBNull.Value ? "0" : dx.Tables[0].Rows[0]["StatusByverificator"].ToString()));
                    p.Notes = dx.Tables[0].Rows[0]["Notes"].ToString();
                    p.isLocked = Convert.ToBoolean(dx.Tables[0].Rows[0]["isLocked"].ToString());
                    p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[0]["isActive"].ToString());
                    p.LastModifiedBy = dx.Tables[0].Rows[0]["LastModifiedBy"].ToString();
                    p.LastModifiedDate = dx.Tables[0].Rows[0]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[0]["LastModifiedDate"].ToString());
                    p.CreateBy = dx.Tables[0].Rows[0]["CreateBy"].ToString();
                    p.CreateDate = dx.Tables[0].Rows[0]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[0]["CreateDate"].ToString());
                    KPIs.Add(p);
                }
                else
                {
                    KPIs.Add(null);
                }
                
            }
            

            return KPIs.ToList();
        }

        public string Save(KPIAchievement KPIAchievements)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_InsertUpdateKPIAchievement";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIAchievements.ID;
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIAchievements.KPIAssign.ID;
            command.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = KPIAchievements.Month;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = KPIAchievements.Year;
            command.Parameters.Add("@REALIZATION", SqlDbType.VarChar).Value = KPIAchievements.Realization;
            command.Parameters.Add("@SCORE", SqlDbType.VarChar).Value = KPIAchievements.Score.Replace(",",".");
            command.Parameters.Add("@STATUSBYUSER", SqlDbType.VarChar).Value = KPIAchievements.StatusByUser == null ? 0 : KPIAchievements.StatusByUser.ID;
            command.Parameters.Add("@STATUSBYVERIFICATOR", SqlDbType.VarChar).Value = KPIAchievements.StatusByVerificator == null ? 0 : KPIAchievements.StatusByVerificator.ID;
            command.Parameters.Add("@NOTES", SqlDbType.VarChar).Value = KPIAchievements.Notes;
            command.Parameters.Add("@ISLOCKED", SqlDbType.VarChar).Value = KPIAchievements.isLocked;
            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIAchievements.isActive;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIAchievements.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIAchievements.CreateBy;

            return ExecuteNonQuery(command);
        }

        public bool Save(List<KPIAchievement> list, List<KPIAttachment> listAttachment, List<KPIAttachment> listAttachmentDelete)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    foreach (KPIAchievement data in list)
                    {
                        string query = "UP_InsertUpdateKPIAchievement";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_InsertUpdateKPIAchievement";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = data.ID;
                        command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = data.KPIAssign.ID;
                        command.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = data.Month;
                        command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = data.Year;
                        command.Parameters.Add("@REALIZATION", SqlDbType.VarChar).Value = data.Realization;
                        command.Parameters.Add("@SCORE", SqlDbType.VarChar).Value = data.Score.Trim()==string.Empty ? "0" : data.Score.Replace(",", ".");
                        command.Parameters.Add("@STATUSBYUSER", SqlDbType.VarChar).Value = data.StatusByUser == null ? 0 : data.StatusByUser.ID;
                        command.Parameters.Add("@STATUSBYVERIFICATOR", SqlDbType.VarChar).Value = data.StatusByVerificator == null ? 0 : data.StatusByVerificator.ID;
                        command.Parameters.Add("@NOTES", SqlDbType.VarChar).Value = data.Notes;
                        command.Parameters.Add("@ISLOCKED", SqlDbType.VarChar).Value = data.isLocked;
                        command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = data.isActive;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = data.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = data.CreateBy;
                        command.Parameters.Add("@MethodDtlId", SqlDbType.VarChar).Value = data.MethodDtlId;

                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    foreach (KPIAttachment data in listAttachment)
                    {

                        string query = "UP_InsertUpdateKPIAttachment";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "UP_InsertUpdateKPIAttachment";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = data.ID;
                        command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = data.KPI.ID;
                        command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = data.KPIAssign.ID;
                        command.Parameters.Add("@KPIASSIGNDETAILID", SqlDbType.VarChar).Value = data.KPIAssignDetail.ID;
                        command.Parameters.Add("@DOCUMENTFILENAME", SqlDbType.VarChar).Value = data.DocumentFileName;
                        command.Parameters.Add("@DOCUMENTNOTES", SqlDbType.VarChar).Value = data.DocumentNotes;
                        command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = data.isActive;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = data.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = data.CreateBy;


                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    foreach (KPIAttachment data in listAttachmentDelete)
                    {

                        string query = "UP_DeleteKPIAttachment";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_InsertUpdateKPIAttachment";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = data.ID;


                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public bool SaveNoAttachment(List<KPIAchievement> list, List<KPIAttachment> listAttachmentDelete)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    foreach (KPIAchievement data in list)
                    {
                        string query = "UP_InsertUpdateKPIAchievement";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_InsertUpdateKPIAchievement";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = data.ID;
                        command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = data.KPIAssign.ID;
                        command.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = data.Month;
                        command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = data.Year;
                        command.Parameters.Add("@REALIZATION", SqlDbType.VarChar).Value = data.Realization;
                        command.Parameters.Add("@SCORE", SqlDbType.VarChar).Value = data.Score.Trim() == string.Empty ? "0" : data.Score.Replace(",", ".");
                        command.Parameters.Add("@STATUSBYUSER", SqlDbType.VarChar).Value = data.StatusByUser == null ? 0 : data.StatusByUser.ID;
                        command.Parameters.Add("@STATUSBYVERIFICATOR", SqlDbType.VarChar).Value = data.StatusByVerificator == null ? 0 : data.StatusByVerificator.ID;
                        command.Parameters.Add("@NOTES", SqlDbType.VarChar).Value = data.Notes;
                        command.Parameters.Add("@ISLOCKED", SqlDbType.VarChar).Value = data.isLocked;
                        command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = data.isActive;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = data.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = data.CreateBy;
                        command.Parameters.Add("@MethodDtlId", SqlDbType.VarChar).Value = data.MethodDtlId;

                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    foreach (KPIAttachment data in listAttachmentDelete)
                    {

                        string query = "UP_DeleteKPIAttachment";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_InsertUpdateKPIAttachment";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = data.ID;


                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }


                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public bool SaveVerificator(List<KPIAchievement> list)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    foreach (KPIAchievement data in list)
                    {
                        string query = "UP_InsertUpdateKPIAchievement_Verificator";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_InsertUpdateKPIAchievement";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = data.ID;
                        command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = data.KPIAssign.ID;
                        command.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = data.Month;
                        command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = data.Year;
                        command.Parameters.Add("@REALIZATION", SqlDbType.VarChar).Value = data.Realization;
                        command.Parameters.Add("@SCORE", SqlDbType.VarChar).Value = data.Score.Trim()==string.Empty? "0" : data.Score.Replace(",", ".");
                        command.Parameters.Add("@STATUSBYUSER", SqlDbType.VarChar).Value = data.StatusByUser == null ? 0 : data.StatusByUser.ID;
                        command.Parameters.Add("@STATUSBYVERIFICATOR", SqlDbType.VarChar).Value = data.StatusByVerificator == null ? 0 : data.StatusByVerificator.ID;
                        command.Parameters.Add("@NOTES", SqlDbType.VarChar).Value = data.Notes;
                        command.Parameters.Add("@ISLOCKED", SqlDbType.VarChar).Value = data.isLocked;
                        command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = data.isActive;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = data.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = data.CreateBy;
                        command.Parameters.Add("@MethodDtlId", SqlDbType.VarChar).Value = data.MethodDtlId;

                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public string UpdateRelease(KPIAchievement KPIAchievements)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_InsertUpdateKPIAchievement_Release";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIAchievements.ID;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIAchievements.LastModifiedBy;
            

            return ExecuteNonQuery(command);
        }

        public KPIAchievement GetKPIAchievementDetail(int month, int year, int KPIAssignID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAchievementDetail";
            command.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = month;
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIAssignID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            IList<string> Data = GetSingleRow(command);
            KPIAchievement Achievement = null;

            if (Data != null && Data.Count > 0)
            {
                Achievement = new KPIAchievement();
                Achievement.ID = Convert.ToInt16(Data[0]);
                Achievement.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt32(Data[1]));
                Achievement.Month = Convert.ToInt16(Data[2]);
                Achievement.Year = Convert.ToInt16(Data[3]);
                Achievement.Realization = Data[4];
                Achievement.Score = Data[5];
                Achievement.StatusByUser = new KPIStatusController().GetKPIStatusByID(Convert.ToInt16(Data[6] == string.Empty ? "0" : Data[6]));
                Achievement.StatusByVerificator = new KPIStatusController().GetKPIStatusByID(Convert.ToInt16(Data[7] == string.Empty ? "0" : Data[7]));
                Achievement.Notes = Data[8];
                Achievement.isActive = Convert.ToBoolean(Data[9]);
                Achievement.isLocked = Convert.ToBoolean(Data[10]);
                Achievement.LastModifiedDate = Data[11] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[11].ToString());
                Achievement.LastModifiedBy = Data[12];
                Achievement.CreateDate = Data[13] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[13].ToString());
                Achievement.CreateBy = Data[14];
            }

            return Achievement;
        }

        public List<KPIAchievement> GetListKPIAchievement(int KPIAssignID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAchievementByKpiAssignID";
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIAssignID;


            DataSet ds = GetTable(command);
            KPIAchievement Achievement =  new KPIAchievement();
            List<KPIAchievement> list = new List<KPIAchievement>();

            if (ds.Tables.Count > 0)
            {
                foreach (DataRow Data in ds.Tables[0].Rows)
                {
                    Achievement = new KPIAchievement();
                    Achievement.ID = Convert.ToInt16(Data[0]);
                    //Achievement.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt32(Data[1]));
                    Achievement.Month = Convert.ToInt16(Data[2]);
                    Achievement.Year = Convert.ToInt16(Data[3]);
                    Achievement.Realization = Data[4].ToString();
                    Achievement.Score = Data[5].ToString();
                    //Achievement.StatusByUser = new KPIStatusController().GetKPIStatusByID(Convert.ToInt16(Data[6].ToString() == string.Empty ? "0" : Data[6]));
                    //Achievement.StatusByVerificator = new KPIStatusController().GetKPIStatusByID(Convert.ToInt16(Data[7].ToString() == string.Empty ? "0" : Data[7]));

                    Achievement.StatusByUser = new KPIStatus();
                    Achievement.StatusByUser.ID = Convert.ToInt16(Data[6].ToString());

                    Achievement.StatusByVerificator = new KPIStatus();
                    Achievement.StatusByVerificator.ID = Convert.ToInt16(Data[7].ToString());

                    Achievement.Notes = Data[8].ToString();
                    Achievement.isActive = Convert.ToBoolean(Data[9]);
                    Achievement.isLocked = Convert.ToBoolean(Data[10]);
                    Achievement.LastModifiedDate = Data[11].ToString() == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[11].ToString());
                    Achievement.LastModifiedBy = Data[12].ToString();
                    Achievement.CreateDate = Data[13].ToString() == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[13].ToString());
                    Achievement.CreateBy = Data[14].ToString();
                    Achievement.MethodDetail.MethodDetailID = Convert.ToInt32(Data[15].ToString());
                    Achievement.MethodDetail.MethodDtlName = Data[16].ToString();
                    Achievement.MethodDetail.MethodID = Convert.ToInt32(Data[17].ToString());
                    Achievement.isOneSelection = Convert.ToBoolean(Data[18].ToString().Trim());
                    

                    list.Add(Achievement);
                }
            }

            return list;
        }

        public string LockKPIAchievement(int month, int year, int KPIAssignID, Employee User)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_LockKPIAchievement";
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIAssignID;
            command.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = month;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@USER", SqlDbType.VarChar).Value = User.Email;

            return ExecuteNonQuery(command);
        }

        public IList<KPIAchievement> GetKPIAchievementByVerificatorID(int Verificator, string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAssignDetailByVerificatorID";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@VERIFICATORID", SqlDbType.VarChar).Value = Verificator;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPIAchievement> KPIs = new List<KPIAchievement>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "KPI":
                            dx.Tables[0].DefaultView.Sort = "DescriptionKPI ASC";
                            break;
                        case "Month":
                            dx.Tables[0].DefaultView.Sort = "Month ASC";
                            break;
                        case "Year":
                            dx.Tables[0].DefaultView.Sort = "Year ASC";
                            break;
                        case "Realization":
                            dx.Tables[0].DefaultView.Sort = "Realization ASC";
                            break;
                        case "Score":
                            dx.Tables[0].DefaultView.Sort = "Score ASC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax ASC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin ASC";
                            break;
                        case "StatusByUser":
                            dx.Tables[0].DefaultView.Sort = "StatusByUser ASC";
                            break;
                        case "StatusByVerificator":
                            dx.Tables[0].DefaultView.Sort = "StatusByVerificator ASC";
                            break;
                        case "Notes":
                            dx.Tables[0].DefaultView.Sort = "Notes ASC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight ASC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "KPI":
                            dx.Tables[0].DefaultView.Sort = "DescriptionKPI DESC";
                            break;
                        case "Month":
                            dx.Tables[0].DefaultView.Sort = "Month DESC";
                            break;
                        case "Year":
                            dx.Tables[0].DefaultView.Sort = "Year DESC";
                            break;
                        case "Realization":
                            dx.Tables[0].DefaultView.Sort = "Realization DESC";
                            break;
                        case "Score":
                            dx.Tables[0].DefaultView.Sort = "Score DESC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax DESC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin DESC";
                            break;
                        case "StatusByUser":
                            dx.Tables[0].DefaultView.Sort = "StatusByUser DESC";
                            break;
                        case "StatusByVerificator":
                            dx.Tables[0].DefaultView.Sort = "StatusByVerificator DESC";
                            break;
                        case "Notes":
                            dx.Tables[0].DefaultView.Sort = "Notes DESC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight DESC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)//(i < dx.Tables[0].Rows.Count)
                    {
                        KPIAchievement p = new KPIAchievement();
                        p.ID = dt.Rows[i]["ID"]==DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        
                        //p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        p.KPIAssign = new KPIAssign();
                        p.KPIAssign.ID = dt.Rows[i]["KPIAssignID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIAssignID"].ToString());
                        p.KPIAssign.KPI = new KPI();
                        p.KPIAssign.KPI.ID = dt.Rows[i]["KPIID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIID"].ToString());
                        p.KPIAssign.KPI.Description = dt.Rows[i]["DescriptionKPI"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionKPI"].ToString();
                        p.KPIAssign.Weight = dt.Rows[i]["Weight"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Weight"].ToString());

                        p.Month = dt.Rows[i]["Month"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Month"].ToString());
                        p.Year = dt.Rows[i]["Year"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Realization = dt.Rows[i]["Realization"] == DBNull.Value ? string.Empty : dt.Rows[i]["Realization"].ToString();
                        p.Score = dt.Rows[i]["Score"] == DBNull.Value ? string.Empty : dt.Rows[i]["Score"].ToString();
                        
                        //p.StatusByUser = new KPIStatusController().GetKPIStatusByID(dt.Rows[i]["StatusByUser"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["StatusByUser"].ToString()));
                        p.StatusByUser = new KPIStatus();
                        p.StatusByUser.Description = dt.Rows[i]["DescriptionStatusUser"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionStatusUser"].ToString();   


                        //p.StatusByVerificator = new KPIStatusController().GetKPIStatusByID(dt.Rows[i]["StatusByVerificator"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["StatusByVerificator"].ToString()));
                        p.StatusByVerificator = new KPIStatus();
                        p.StatusByVerificator.Description = dt.Rows[i]["DescriptionStatusVerificator"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionStatusVerificator"].ToString();


                        p.KPIAssign.UoMDetailMax = new UoMDetail();
                        p.KPIAssign.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.KPIAssign.UoMDetailMin = new UoMDetail();
                        p.KPIAssign.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMin"].ToString();


                        p.Notes = dt.Rows[i]["Notes"] == DBNull.Value ? string.Empty : dt.Rows[i]["Notes"].ToString();
                        p.isActive = dt.Rows[i]["isActive"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        p.isLocked = dt.Rows[i]["isLocked"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["isLocked"].ToString());
                        //p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        //p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["CreateBy"].ToString();
                        //p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dx.Tables[0].Rows.Count;

            return KPIs.ToList();
        }
    }
}